﻿Imports System.Data.SqlClient
Imports System.Data

Imports Excel = Microsoft.Office.Interop.Excel

Public Class frmExportExcel

    Dim strcn As String = "Data Source=.\SQLEXPRESS;Initial Catalog=QLKS;Integrated Security=True"
    Dim con As New SqlConnection(strcn)


    Private Sub Label2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label2.Click
        Me.Close()
    End Sub

    Private Sub loadcbx()

        ComboBox1.Items.Add("Hóa Đơn")
        ComboBox1.Items.Add("Phiếu thuê")
        ComboBox1.Items.Add("Danh sách phòng")
    End Sub

    Private Sub frmExportExcel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        loadcbx()
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        If (ComboBox1.SelectedIndex = 0) Then
            LoadHoaDon()
        ElseIf (ComboBox1.SelectedIndex = 1) Then
            LoadPhieuThue()
        ElseIf (ComboBox1.SelectedIndex = 2) Then
            LoadDanhSachPhong()
        End If
    End Sub

    Private Sub LoadHoaDon()
        Dim sql As String = "select * from HoaDon"
        Dim da As New SqlDataAdapter(sql, con)
        Dim dt As New DataTable()
        da.Fill(dt)
        DataGridView1.DataSource = dt
    End Sub
    Private Sub LoadPhieuThue()
        Dim sql As String = "select * from PhieuThue"
        Dim da As New SqlDataAdapter(sql, con)
        Dim dt As New DataTable()
        da.Fill(dt)
        DataGridView1.DataSource = dt
    End Sub
    Private Sub LoadDanhSachPhong()
        Dim sql As String = "select * from Phong"
        Dim da As New SqlDataAdapter(sql, con)
        Dim dt As New DataTable()
        da.Fill(dt)
        DataGridView1.DataSource = dt
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer


        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    DataGridView1(j, i).Value.ToString()
            Next
        Next
        Dim filename As String = txtfilename.Text
        xlWorkSheet.SaveAs("C:\vbexcel_" & filename & ".xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox(" File được lưu ở C:\excel_" & filename & ".xlsx")
    End Sub


    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
   
End Class